package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.*;

/**
 *
 * @author Tony
 */
public class ItemDAO {

  private Connection conn = null;
  private Statement sqlStatement;
  private static final Logger logger = Logger.getLogger(ItemDAO.class.getName());

  /**
   *
   * @param conn
   * @param sqlStatement
   */
  public ItemDAO(Connection conn, Statement sqlStatement) {
    this.conn = conn;
    this.sqlStatement = sqlStatement;
  }

  public boolean addItem(String upc,
                         String name,
                         double costPrice,
                         double retailPrice,
                         String description,
                         String make,
                         String model,
                         String imgURL, int ctid) {

    String sqlText = "INSERT INTO `digistore`.`item` "
      + "(`upc`, `itemName`, `costPrice`, `retailPrice`, "
      + "`description`, `make`, `model`, `imgURL`, `ctid`) VALUES "
      + "('" + upc + "', "
      + "'" + name + "', "
      + "'" + costPrice + "', "
      + "'" + retailPrice + "', "
      + "'" + description + "', "
      + "'" + make + "', "
      + "'" + model + "', "
      + "'" + imgURL + "', "
      + ctid + ")";
    return updateDB(sqlText);
  }

  public boolean deleteItem(String upc) {
    String sqlText = "DELETE FROM `digistore`.`item` WHERE `upc`='"
      + upc + "'";
    return updateDB(sqlText);
  }

  public boolean updateItem(String originalUPC,
                            String upc,
                            String name,
                            double costPrice,
                            double retailPrice,
                            String description,
                            String make,
                            String model,
                            String imgURL, int ctid) {
    String sqlText =  "UPDATE `digistore`.`item` SET "
      + "`upc`='" + upc + "', "
      + "`itemName`='" + name + "', "
      + "`costPrice`='" + costPrice + "', "
      + "`retailPrice`='" + retailPrice + "', "
      + "`description`='" + description + "', "
      + "`make`='" + make + "', "
      + "`model`='" + model + "', "
      + "`imgURL`='" + imgURL + "', "
      + "`ctid`=" + ctid
      + " WHERE `upc`='" + originalUPC + "'";
    return updateDB(sqlText);
  }

  public boolean addItemToStore(String storeName, String upc) {
    String sqlText = "INSERT INTO `digistore`.`iteminstore` "
      + "(`storeName`, `upc`, `qty`) VALUES ("
      + "'" + storeName + "', "
      + "'" + upc + "', "
      + "0)";
    return updateDB(sqlText);
  }

  public void addItemToAllStore(String upc) {
    PreparedStatement ps = null;
    String sqlText = null;

    try {
      ps = conn.prepareStatement("insert into digistore.iteminstore values (?,?,?)");

      sqlText = "select storeName from digistore.store";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      while (rs.next()) {
        String st = rs.getString(1);
        ps.setString(1, st);
        ps.setString(2, upc);
        ps.setInt(3, 0);
        if (ps.executeUpdate() == 1) {
          conn.commit();
        }
      }
      ps.close();
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ItemDAO : addItemToAllStore" + ex);
    }
  }

  public boolean updateItemQTYInStore(String storeName, String upc, int qty) {
    String sqlText = "UPDATE `digistore`.`iteminstore` SET `qty`=" + qty
      + " WHERE `storeName`='" + storeName + "' and `upc`='" + upc + "'";
    return updateDB(sqlText);
  }

  public ArrayList<Item> searchWithKeyWord(String word) {
    ArrayList<Item> list = new ArrayList<Item>();
    try {
      String sqlText = "select it.upc, it.itemName, it.description, "
        + "it.make, it.model, it.retailPrice, it.imgURL "
        + "from digistore.item it where "
        + "it.itemName like '%" + word + "%'" + " or "
        + "it.description like '%" + word + "%'" + " or "
        + "it.make like '%" + word + "%'" + " or "
        + "it.model like '%" + word + "%'";

      ResultSet rs = sqlStatement.executeQuery(sqlText);
      while (rs.next()) {
        list.add(new Item(rs.getString(1),
                          rs.getString(2),
                          rs.getString(3),
                          rs.getString(4),
                          rs.getString(5),
                          rs.getFloat(6),
                          rs.getString(7)));
      }
      return list;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ItemDAO : searchWithKeyWord" + ex);
    }
    return list;
  }

  public ArrayList<Item> getItemsByCategory(int ctid) {
    ArrayList<Item> list = new ArrayList<Item>();
    try {
      String sqlText = "select it.upc, it.itemName, it.description, "
        + "it.make, it.model, it.retailPrice, it.imgURL "
        + "from digistore.item it, digistore.category c "
        + "where it.ctid = c.ctid and it.ctid in ("
        + "select temp.ctid "
        + "from ("
        + "select c.ctid "
        + "from digistore.category c "
        + "where c.ctid not in ("
        + "select distinct c1.pctid "
        + "from digistore.category c1 "
        + "where c1.pctid is not NULL)) temp "
        + "where temp.ctid like '" + ctid + "%')";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      while (rs.next()) {
        list.add(new Item(rs.getString(1),
                          rs.getString(2),
                          rs.getString(3),
                          rs.getString(4),
                          rs.getString(5),
                          rs.getFloat(6),
                          rs.getString(7)));
      }
      return list;
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ItemDAO : getItemByCategory" + ex);
    }

    return list;
  }

  public int getItemQtyInStore(String storeName, String upc) {
    int qty = -1;
    try {
      String sqlText = "select a.qty "
        + "from digistore.iteminstore a "
        + "where a.storeName = '" + storeName
        + "' and a.upc='" + upc + "'";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      if (rs.next()) {
        return rs.getInt(1);
      }
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ItemDAO : getItemQtyInStore" + ex);
    }
    return qty;
  }

  public boolean hasNoChild(int ctid) {
    try {
      String sqlText = "select c.ctid "
        + "from digistore.category c "
        + "where c.ctid=" + ctid + " and c.ctid not in ("
        + "select distinct c1.pctid "
        + "from digistore.category c1 "
        + "where c1.pctid is not NULL)";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      return rs.next();
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ItemDAO : hasChild" + ex);
    }
    return false;
  }

  private boolean updateDB(String sqlText) {
    try {
      int value = sqlStatement.executeUpdate(sqlText);
      if (value == 1) {
          conn.commit();
          return true;
      }
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ItemDAO : updateDB" + ex);
    }

    return false;
  }

  public static String getQueryString(String attribute, String keywords) {
    String result = "";
    StringTokenizer st = new StringTokenizer(keywords.trim());
    while (st.hasMoreTokens()) {
      result += attribute + " like '%" + st.nextToken() + "%'";
      if (st.hasMoreTokens()) {
        result += " or ";
      }
    }
    return result;
  }

}
